package com.huabo.contract.oracle.mapper;

import com.hbfk.util.DateUtil;
import com.hbfk.util.PageInfo;
import com.huabo.contract.oracle.entity.TblContractLend;

public class TblContractLendMapperSqlConfig {

	public String updateTblContractLead(TblContractLend lend) {
		StringBuffer sql = new StringBuffer("UPDATE TBL_CONTRACT_LEND SET CONTRACTID = '"+lend.getContractid()+"'");
		if(lend.getUserid() != null && !"".equals(lend.getUserid())) {
			sql.append(" , USERID = '"+lend.getUserid()+"'");
		}
		if(lend.getMemo() != null && !"".equals(lend.getMemo())) {
			sql.append(" , MEMO = '"+lend.getMemo()+"'");
		}
		if(lend.getLendstatus() != null && !"".equals(lend.getLendstatus())) {
			sql.append(" , LENDSTATUS = '"+lend.getLendstatus()+"'");
		}
		if(lend.getLenddate() != null) {
			sql.append(" ,LENDDATE = TO_DATE('"+DateUtil.parseDate(lend.getLenddate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
		}
		if(lend.getReturndate() != null) {
			sql.append(" ,RETURNDATE = TO_DATE('"+DateUtil.parseDate(lend.getReturndate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
		}
		sql.append(" WHERE LENDID = '"+lend.getLendid()+"'");
		return sql.toString();
	}
	
	
	public String saveTblContractLead(TblContractLend lend) {
		StringBuffer column = new StringBuffer("INSERT INTO TBL_CONTRACT_LEND (LENDID");
		StringBuffer value = new StringBuffer(" VALUES (HIBERNATE_SEQUENCE.nextval ");

		if(lend.getContractid() != null) {
			column.append(",CONTRACTID");
			value.append(",'"+lend.getContractid()+"'");
		}
		if(lend.getUserid() != null) {
			column.append(",USERID");
			value.append(",'"+lend.getUserid()+"'");
		}
		if(lend.getMemo() != null) {
			column.append(",MEMO");
			value.append(",'"+lend.getMemo()+"'");
		}
		if(lend.getLendstatus() != null) {
			column.append(",LENDSTATUS");
			value.append(",'"+lend.getLendstatus()+"'");
		}
		if(lend.getLenddate() != null) {
			column.append(",LENDDATE");
			value.append(",TO_DATE('"+DateUtil.parseDate(lend.getLenddate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
		}
		if(lend.getReturndate() != null) {
			column.append(",RETURNDATE");
			value.append(",TO_DATE('"+DateUtil.parseDate(lend.getReturndate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
		}

		column.append(")");
		value.append(")");
		String sql = column.toString()+value.toString();
		return sql;
	}

	public String findByContractId(PageInfo<TblContractLend> pageInfo) throws Exception {
		TblContractLend contractLend = pageInfo.getCondition();
		StringBuffer sbSql = new StringBuffer("SELECT * FROM ( SELECT BUDGET.*,ROWNUM RNUM FROM (SELECT TC.LENDID,TC.LENDDATE,TC.RETURNDATE,TC.MEMO,TC.LENDSTATUS,TCY.CONTRACTNAME,TCY.CONTRACTNO,TS.STAFFID,TS.REALNAME FROM TBL_CONTRACT_LEND TC LEFT JOIN TBL_CYHW_UNIT TCY ON TC.CONTRACTID = TCY.CONTRACTID LEFT JOIN TBL_STAFF TS ON TC.USERID = TS.STAFFID WHERE");
		if (contractLend.getContractid() != null && !"".equals(contractLend.getContractid())) {
			sbSql.append(" TC.CONTRACTID = " + contractLend.getContractid());
		}
		sbSql.append(" ORDER BY TC.LENDID DESC) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RNUM > "+pageInfo.getCurrentRecord());
		String sql = sbSql.toString();
		return sql;
	}
	public String findCountByContractId(PageInfo<TblContractLend> pageInfo) throws Exception {
		TblContractLend contractLend = pageInfo.getCondition();
		StringBuffer sbSql = new StringBuffer("SELECT COUNT(*) FROM TBL_CONTRACT_LEND TC LEFT JOIN TBL_CYHW_UNIT TCY ON TC.CONTRACTID = TCY.CONTRACTID LEFT JOIN TBL_STAFF TS ON TC.USERID = TS.STAFFID WHERE");
		if (contractLend.getContractid() != null && !"".equals(contractLend.getContractid())) {
			sbSql.append(" TC.CONTRACTID = " + contractLend.getContractid());
		}
		return sbSql.toString();
	}





}
